package imagekeeper.data;

import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import javax.sql.DataSource;
import imagekeeper.dmi.Image;
import imagekeeper.dmi.ImageManager;
import imagekeeper.data.mapping.ImageMapper;


class ImageManagerImpl extends AbstractEntityManager implements ImageManager  {
    
    public ImageManagerImpl(DataSource ds) {
        dataSource = ds;
    }

    @Override
    public Image find(int id) throws SQLException {
        Connection con = null;
        SqlStatementSelectImage sqlSt = null;
        Image result = null;
        try {
            con = dataSource.getConnection();
            sqlSt = new SqlStatementSelectImage(con, id);
            ResultSet rs = sqlSt.executeQuery();
            List<Image> imgList = ImageMapper.getImages(rs);
            if (!imgList.isEmpty())
                result = imgList.get(0);
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
        return result;
    }

    @Override
    public Image find(String url) throws SQLException {
        Connection con = null;
        SqlStatementSelectImageByUrl sqlSt = null;
        Image result = null;
        try {
            con = dataSource.getConnection();
            sqlSt = new SqlStatementSelectImageByUrl(con, url);
            ResultSet rs = sqlSt.executeQuery();
            List<Image> imgList = ImageMapper.getImages(rs);
            if (!imgList.isEmpty())
                result = imgList.get(0);
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
        return result;
    }

    @Override
    public List<Image> find(String tags, int offset, int resultCount) throws SQLException {
        Connection con = null;
        SqlStatementSearchImages sqlSt = null;
        List<Image> result = new ArrayList<Image>();
        try {
            con = dataSource.getConnection();
            con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);            
            sqlSt = new SqlStatementSearchImages(con);
            sqlSt.setSearchKey(tags);
            sqlSt.setResultLimits(offset, resultCount);
            ResultSet rs = sqlSt.executeQuery();
            result = ImageMapper.getImages(rs);
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
        return result;
    }
    
    @Override
    public List<Image> find(int[] idSubset) throws SQLException {
        Connection con = null;
        Statement sqlSt = null;
        List<Image> result = new ArrayList<Image>();
        try {
            con = dataSource.getConnection();
            con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            sqlSt = con.createStatement();
            String sqlRequest = "select * from " + ImageMapper.TableName + " where " 
                    + ImageMapper.ColumnId + " in (";
            for (int index = 0; index < idSubset.length; index++) {
                sqlRequest += String.valueOf(idSubset[index]);
                if (index < idSubset.length - 1)
                    sqlRequest += ",";
            }
            sqlRequest += ");";
            ResultSet rs = sqlSt.executeQuery(sqlRequest);
            result = ImageMapper.getImages(rs);
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) closeStatement(sqlSt);
            if (con != null) closeConnection(con);
        }
        return result;
    }

    @Override
    public void create(Object newImage) throws SQLException {
        Connection con = null;
        SqlStatementCreateImage sqlSt = null;
        Image img = (Image)newImage;
        ImageTagManagerImpl imgTagMng = new ImageTagManagerImpl(dataSource);
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            sqlSt = new SqlStatementCreateImage(con, img);
            sqlSt.executeUpdate();
            img.setId(sqlSt.getImageId());
            imgTagMng.create(img, con);
            con.commit();
        }
        catch (SQLException ex) {
            if (con != null) rollbackTransaction(con);
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
    }

    @Override
    public void remove(Object image) throws SQLException {
        Connection con = null;
        SqlStatementRemoveImage sqlSt = null;
        try {
            con = dataSource.getConnection();
            sqlSt = new SqlStatementRemoveImage(con, ((Image)image).getId());
            sqlSt.executeUpdate();
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
    }
    
    @Override
    public void removeByLink(String removingLink) throws SQLException {
        Connection con = null;
        SqlStatementRemoveImageByLink sqlSt = null;
        try {
            con = dataSource.getConnection();
            sqlSt = new SqlStatementRemoveImageByLink(con, removingLink);
            sqlSt.executeUpdate();
        }
        catch (SQLException ex) {
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
    }

    @Override
    public void refresh(Object image) throws SQLException {
        Connection con = null;
        SqlStatementUpdateImage sqlSt = null;
        try {
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            sqlSt = new SqlStatementUpdateImage(con, (Image)image);
            sqlSt.executeUpdate();
            ImageTagManagerImpl imgTagMng = new ImageTagManagerImpl(dataSource);
            imgTagMng.updateImageTags((Image)image, con);
            con.commit();
        }
        catch (SQLException ex) {
            if (con != null)
                rollbackTransaction(con);
            throw ex;
        }
        finally {
            if (sqlSt != null) sqlSt.close();
            if (con != null) closeConnection(con);
        }
    }
}
